package com.wuliu.dao.impl;

import com.wuliu.dao.BusinessDAO;
import com.wuliu.model.Business;
import com.wuliu.model.BusinessClass;
import com.wuliu.util.Jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


public class BusinessDAOImpl implements BusinessDAO {
    @Override
    public List<Business> findAllAndClass() {
        List<Business> businesses =new ArrayList<>();
        try {
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql="SELECT * FROM `business`,`business_class`  where business.cid=business_class.id";
            PreparedStatement statement=connection.prepareStatement(sql);
            ResultSet set=statement.executeQuery();
            while (set.next()){
                int id=set.getInt("id");
                String img=set.getString("img");
                String name=set.getString("name");
                String desc=set.getString("desc");
                String time=set.getString("time");
                int cid=set.getInt("cid");

                int businessClassId=set.getInt("business_class.id");
                String businessClassName=set.getString("business_class.name");
                String businessClassDesc=set.getString("business_class.desc");
                String businessClassImg =set.getString("business_class.img");
                BusinessClass businessClass=new BusinessClass(businessClassId,businessClassName,businessClassDesc,businessClassImg);
                Business business = new Business(id,img,name,desc,time,cid,businessClass);
                businesses.add(business);

            }
            set.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return businesses;
    }

    @Override
    public Business findByIdAndClass(int id) {
        Business business =null;

        try {
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql="select * from `business`,`business_class` where business.id=? and business.cid=business_class.id";
            PreparedStatement statement=connection.prepareStatement(sql);
            statement.setInt(1,id);
            ResultSet set=statement.executeQuery();
            while (set.next()){
                String img=set.getString("img");
                String name=set.getString("name");
                String desc=set.getString("desc");
                String time=set.getString("time");
                int cid=set.getInt("cid");
                int businessClassId=set.getInt("business_class.id");
                String businessClassName=set.getString("business_class.name");
                String businessClassDesc=set.getString("business_class.desc");
                String businessClassImg =set.getString("business_class.img");
                BusinessClass businessClass=new BusinessClass(businessClassId,businessClassName,businessClassDesc,businessClassImg);

                business=new Business(id,img,name,desc,time,cid,businessClass);
            }
            set.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return business;
    }

    @Override
    public void add(Business business) {
        try {
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql="insert into business values(null,?,?,?,?,?)";
            PreparedStatement statement=connection.prepareStatement(sql);
            statement.setString(1,business.getImg());
            statement.setString(2,business.getName());
            statement.setString(3,business.getDesc());
            statement.setString(4,business.getTime());
            statement.setInt(5,business.getCid());
            statement.execute();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    @Override
    public void deleteById(int id) {
        try {
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql="delete from business where id=?";
            PreparedStatement statement=connection.prepareStatement(sql);
            statement.setInt(1,id);
            statement.execute();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }



    }

    @Override
    public void update(Business business) {
        try {

            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql="update business set img=?,name=?,`desc`=?,time=?,cid=? where id=?";
            PreparedStatement statement=connection.prepareStatement(sql);
            statement.setString(1,business.getImg());
            statement.setString(2,business.getName());
            statement.setString(3,business.getDesc());
            statement.setString(4,business.getTime());
            statement.setInt(5,business.getCid());
            statement.setInt(6,business.getId());
            statement.execute();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    @Override
    public Business findById(int id) {
        Business business =null;
        try {
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql = "select * from business where id= ?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            ResultSet set = statement.executeQuery();
            while (set.next()) {

                String img=set.getString("img");
                String name=set.getString("name");
                String desc=set.getString("desc");
                String time=set.getString("time");
                int cid =set.getInt("cid");
                business=new Business(id,img,name,desc,time,cid);

            }
            set.close();
            statement.close();
            connection.close();


        } catch (Exception e) {
            e.printStackTrace();
        }

        return  business;

    }

    @Override
    public List<Business> findByCid(int cid) {
        List<Business> businesses=new ArrayList<>();
        try {
            Jdbc jdbc=new Jdbc();
            Connection connection=jdbc.connection();
            String sql="select * from business where cid=?";
            PreparedStatement statement=connection.prepareStatement(sql);
            statement.setInt(1,cid);
            ResultSet set=statement.executeQuery();
            while (set.next()){
                int id=set.getInt("id");
                String img=set.getString("img");
                String name=set.getString("name");
                String desc=set.getString("desc");
                String time=set.getString("time");

                Business business =new Business(id,img,name,desc,time,cid);
                businesses.add(business);

            }
            set.close();
            statement.close();
            connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return businesses;

    }

    @Override
    public List<Business> findAll() {
        List<Business> businesses=new ArrayList<>();
        try {
            Jdbc jdbc=new Jdbc();
            Connection connection =jdbc.connection();
            String sql="select * from business";
            PreparedStatement statement=connection.prepareStatement(sql);
            ResultSet set=statement.executeQuery();
            while (set.next()){
                int id =set.getInt("id");
                String img=set.getString("img");
                String name=set.getString("name");
                String desc=set.getString("desc");
                String time=set.getString("time");
                int cid =set.getInt("cid");
                Business business=new Business(id,img,name,desc,time,cid);
                businesses.add(business);
            }
            set.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return businesses;
    }


}

